package db

import (
	"context"
	"dag2.0/gat1400-proxy/cache"
	"dag2.0/gat1400-proxy/logger"
	"dag2.0/gat1400-proxy/model"
	"dag2.0/gat1400-proxy/model/constant"
	"dag2.0/gat1400-proxy/util"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	"log"
	"os"
	"runtime"
	"strings"
	"time"
)

const (
	BATCH_COUNT = 1000
	OK          = 0
	FAILUER     = -1
)

var Conn *sqlx.DB

func NewDbWorkerConnect() {
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/data_subscribe?multiStatements=true&charset=utf8", userName, password, dsn)
	//dataSourceName = dataSourceName + "?parseTime=true&loc=Asia%2FShanghai&charset=utf8"
	db, err := sqlx.Connect("mysql", dataSourceName)
	if err != nil {
		logger.LOG_ERROR("连接数据库失败 :  ", err)
		log.Fatalln(err)
	}
	db.SetMaxIdleConns(5) // 设置理想最大连接数
	Conn = db
}

var userName string
var password string
var dsn string

func init() {
	dsn = os.Getenv("DOCKER_DB_SERVER")
	userName = os.Getenv("DOCKER_DB_USERNAME")
	password = os.Getenv("DOCKER_DB_PASSWORD")
	logger.LOG_INFO("dsn: ", dsn)
	//创建数据库 data_subscribe
	err := createDataSubscribeDataBase()
	if err != nil {
		logger.LOG_ERROR("createDataSubscribeDataBase error：", err)
		log.Panic()
	}
	time.Sleep(3 * time.Second)
	//建表
	err = createTable()
	if err != nil {
		logger.LOG_ERROR("updateTable error：", err)
		log.Panic()
	}
	//初始化数据库链接
	NewDbWorkerConnect()
	//执行update
	err = updateTable()
	if err != nil {
		logger.LOG_ERROR("updateTable error：", err)
		log.Panic()
	}
	// subscribe 表添加FailDetail列
	err = addFailDetailColumn()
	if err != nil {
		logger.LOG_ERROR("add failDetailColumn error：", err)
		log.Panic()
	}
	InitCache()
}

func addFailDetailColumn() error {
	strSql := "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name='subscribe' AND COLUMN_NAME='FailDetail';"
	rows, err := Conn.Query(strSql)
	if err != nil {
		return err
	}
	// 判断列是否存在
	exist := rows.Next()
	if exist {
		return nil
	}

	addStr := "ALTER TABLE subscribe ADD COLUMN FailDetail VARCHAR ( 400 ) DEFAULT '' COMMENT '订阅失败详情';"
	_, err = Conn.Exec(addStr)
	if err != nil {
		return err
	}

	return nil
}

func updateTable() error {
	var tableNamge = ""
	s := "SELECT table_name FROM information_schema.TABLES WHERE table_name ='device_push';"
	e := Conn.Get(&tableNamge, s)
	switch {
	case errors.Cause(e) == sql.ErrNoRows:
	case e != nil:
		if _, file, line, ok := runtime.Caller(0); ok {
			logger.LOG_ERROR("select columns error  ,", e, file, line)
		}
		return e
	}
	//已存在，不需要添加字段
	if tableNamge != "" {
		return nil
	}
	s = "CREATE TABLE `device_push` (" +
		"`resourceId` varchar(50) NOT NULL COMMENT '资源id，采集设备、卡口、车道'," +
		"`pushTime` bigint(13) DEFAULT NULL COMMENT '推送时间'," +
		"`resourceType` varchar(2) NOT NULL COMMENT '资源类型 1：采集设备 2：卡口 3：车道'," +
		"`managerId` varchar(50) NOT NULL COMMENT '视图库id'," +
		"PRIMARY KEY (`resourceId`,`resourceType`,`managerId`)" +
		") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
	_, e = Conn.Exec(s)
	if e != nil {
		logger.LOG_ERROR("exec create device_push error  ,", e)
		return e
	}
	return nil
}
func SelectPushRecordByManager(managerId string) ([]*model.DevicePush, error) {
	sqlStr := "SELECT * FROM device_push WHERE managerId=?"
	var devicePushes = make([]*model.DevicePush, 0)
	err := Conn.Select(&devicePushes, sqlStr, managerId)
	if err != nil {
		logger.LOG_ERROR("查询已绑定资源失败 ", err)
		return nil, err
	}
	return devicePushes, nil
}
func SelectPushRecordByManagerAndType(managerId string, resourceType string) (map[string]*model.DevicePush, error) {
	sqlStr := "SELECT * FROM device_push WHERE managerId=? and resourceType =?"
	var devicePushes = make([]*model.DevicePush, 0)
	var devicePusheMap = make(map[string]*model.DevicePush, 0)
	err := Conn.Select(&devicePushes, sqlStr, managerId, resourceType)
	if err != nil {
		logger.LOG_ERROR("查询已绑定资源失败 ", err)
		return nil, err
	}
	for _, devicePushe := range devicePushes {
		devicePusheMap[devicePushe.ResourceId] = devicePushe
	}
	return devicePusheMap, nil
}
func DeleteDevicePushTime(devices []*model.DevicePush) error {
	sql := "DELETE FROM device_push WHERE resourceId = ? and resourceType=? and managerId = ? "
	for _, device := range devices {
		_, e := Conn.Exec(sql, device.ResourceId, device.ResourceType, device.MangerId)
		if e != nil {
			logger.LOG_ERROR("执行sql失败,", e)
			return e
		}
	}
	return nil
}

func UpdateDevicePushTime(updateDevices []*model.DevicePush, deleteDevices []*model.DevicePush) error {
	tx, err := Conn.Beginx() // 开启事务
	if err != nil {
		logger.LOG_ERROR("begin trans failed, err:  ", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			logger.LOG_ERROR("panic  rollback , ", p)
		} else if err != nil {
			logger.LOG_ERROR("rollback")
			_ = tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			logger.LOG_WARN("更新数据成功！ commit")
		}
	}()
	//更新
	if len(updateDevices) > 0 {
		// 存放 (?, ?) 的slice
		valueStrings := make([]string, 0, len(updateDevices))
		// 存放values的slice
		valueArgs := make([]interface{}, 0, len(updateDevices)*3)
		// 组装数据
		for _, device := range updateDevices {
			// 此处占位符要与插入值的个数对应
			valueStrings = append(valueStrings, "(?,?,?,?)")
			valueArgs = append(valueArgs, device.ResourceId)
			valueArgs = append(valueArgs, device.PushTime)
			valueArgs = append(valueArgs, device.ResourceType)
			valueArgs = append(valueArgs, device.MangerId)
		}
		stmt := fmt.Sprintf("replace into device_push(resourceId,pushTime,resourceType,managerId) values %s",
			strings.Join(valueStrings, ","))
		_, err = tx.Exec(stmt, valueArgs...)
		if err != nil {
			logger.LOG_ERROR("执行sql失败,error : ", err, "  sql 为 : ", stmt)
			return err
		}
	}

	if len(deleteDevices) > 0 {
		//删除
		s := "DELETE FROM device_push WHERE resourceId = ? and resourceType=? and managerId = ? "
		for _, device := range deleteDevices {
			_, e := tx.Exec(s, device.ResourceId, device.ResourceType, device.MangerId)
			if e != nil {
				logger.LOG_ERROR("执行sql失败,", e)
				return e
			}
		}
	}
	return nil
}

func createDataSubscribeDataBase() error {
	db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/?multiStatements=true&charset=utf8", userName, password, dsn))
	//db, err := sql.Open("mysql", "root:Netposa123@tcp(172.16.129.54:3307)/?multiStatements=true&charset=utf8")
	if err != nil {
		logger.LOG_ERROR("连接数据库失败！", err)
		return err
	}
	defer db.Close()
	dataSubscribeSql := "Create Database If Not Exists data_subscribe Character Set UTF8;"
	_, err = db.Exec(dataSubscribeSql)
	if err != nil {
		logger.LOG_ERROR("创建数据库 data_subscribe 失败！", err)
		return err
	}
	return nil
}

func createTable() error {
	db, err := InitDB()
	if err != nil {
		logger.LOG_ERROR("连接数据库失败！", err)
		return err
	}
	defer db.Close()
	tableSql := getCreateTableSql()
	_, err = db.Exec(tableSql)
	if err != nil {
		logger.LOG_ERROR("创建数据库表失败!", err)
		return err
	}
	return nil
}

func getCreateTableSql() string {
	subscribe_sql := "CREATE TABLE  IF Not Exists `subscribe` (" +
		"`SubscribeID` varchar(100) NOT NULL COMMENT '订阅标识符'," +
		"`Title` varchar(255) DEFAULT NULL COMMENT '描述订阅的主题和目标，订阅时必选'," +
		"`SubscribeDetail` varchar(36) DEFAULT NULL COMMENT '订阅类别，订阅时必选'," +
		"`ResourceURI` text COMMENT '订阅资源路径'," +
		"`ApplicantName` varchar(36) DEFAULT NULL COMMENT '申请人，订阅时必选'," +
		"`ApplicantOrg` varchar(36) DEFAULT NULL COMMENT '申请单位名称'," +
		"`BeginTime` varchar(36) DEFAULT NULL COMMENT '开始时间'," +
		"`EndTime` varchar(36) DEFAULT NULL COMMENT '结束时间'," +
		"`ReceiveAddr` varchar(255) DEFAULT NULL COMMENT '订阅信息接收地址'," +
		"`ReportInterval` int(11) DEFAULT NULL COMMENT '信息上报时间间隔'," +
		"`Reason` varchar(255) DEFAULT NULL COMMENT '进行该订阅的理由'," +
		"`OperateType` varchar(36) DEFAULT NULL COMMENT '操作类型，0订阅，1取消订阅'," +
		"`SubscribeStatus` varchar(36) DEFAULT NULL COMMENT '订阅执行状态，0订阅中，1已取消订阅，2订阅到期，9未订阅,99完成订阅,-1订阅失败'," +
		"`SubscribeCancelOrg` varchar(36) DEFAULT NULL COMMENT '订阅取消单位，仅在取消时使用'," +
		"`SubscribeCancelPerson` varchar(36) DEFAULT NULL COMMENT '订阅取消人，仅在取消时使用'," +
		"`CancelTime` varchar(36) DEFAULT NULL COMMENT '订阅取消人，仅在取消时使用'," +
		"`CancelReason` varchar(255) DEFAULT NULL COMMENT '取消原因，仅在取消时使用'," +
		"`TabID` varchar(36) DEFAULT NULL COMMENT '订阅分类标签标识'," +
		"`ManagerID` varchar(20) DEFAULT NULL COMMENT '视图库ID'," +
		"`ManagerName` varchar(100) DEFAULT NULL COMMENT '视图库名称'," +
		"`UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
		"`InsertTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间'," +
		"`FailDetail` varchar(400) DEFAULT NULL COMMENT '订阅失败详情'," +
		"PRIMARY KEY (`SubscribeID`)" +
		") ENGINE=InnoDB DEFAULT CHARSET=utf8;"

	subscribe_manager_sql := "CREATE TABLE IF Not Exists `subscribe_manager` (" +
		"`manager_id` varchar(20) NOT NULL COMMENT '视图库ID'," +
		"`self_manager_id` varchar(20) NOT NULL COMMENT '本级视图库ID'," +
		"`area_code` varchar(20) DEFAULT NULL COMMENT '区域编码'," +
		"`area_name` varchar(60) DEFAULT NULL COMMENT '区域名称'," +
		"`manager_name` varchar(60) DEFAULT '' COMMENT '视图库名称'," +
		"`manager_ip` varchar(20) NOT NULL COMMENT '视图库IP'," +
		"`manager_port` varchar(20) NOT NULL COMMENT '视图库端口'," +
		"`receive_addr` varchar(255) DEFAULT NULL COMMENT '数据接收地址'," +
		"`user_name` varchar(60) DEFAULT NULL COMMENT '视图库账号'," +
		"`password` varchar(60) DEFAULT NULL COMMENT '视图库密码'," +
		"`is_online` varchar(1) DEFAULT '' COMMENT '是否在线  0:否 1:是'," +
		"`device_status` int(4) DEFAULT '0' COMMENT '0:关闭 1:开启'," +
		"`data_status` int(4) DEFAULT '0' COMMENT '0:关闭 1:开启'," +
		"`manager_remark` varchar(255) DEFAULT NULL COMMENT '备注'," +
		"`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
		"`insert_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间'," +
		"`manager_type` varchar(32) DEFAULT NULL COMMENT '订阅类型（1-下级 2-上级）'," +
		"PRIMARY KEY (`manager_id`) USING BTREE" +
		") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='视图库管理';"

	view_info_sql := "CREATE TABLE IF Not Exists`view_info` (" +
		"`MANAGERID` varchar(20) NOT NULL COMMENT '视图库ID'," +
		"`TYPE_` varchar(1) NOT NULL COMMENT '类型 0-设备 1-卡口 2-任务'," +
		"`RESOURCE_ID` varchar(50) DEFAULT NULL COMMENT '资源id'" +
		") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='视图库信息';"

	return fmt.Sprintf("%s\n%s\n%s", subscribe_sql, subscribe_manager_sql, view_info_sql)
}

func InitCache() {
	_ = util.Retry(func() error {
		//查询视图库
		managers, err := QueryAllManager()
		if err != nil {
			logger.LOG_ERROR("查询视图库列表失败  ", err)
			return err
		}
		//根据视图库id查询已绑定的资源
		for _, manager := range managers {
			var eqs = make([]string, 0)
			var tls = make([]string, 0)
			var tks = make([]string, 0)
			if manager.ManagerType == constant.MANAGER_UP {
				views, err := QueryByViewId(manager.ManagerID)
				if err != nil {
					logger.LOG_ERROR("根据视图库id查询已绑定资源失败", err)
					return err
				}
				eqs = views.EquipmentIds
				tls = views.TollgateIds
				tks = views.TaskIds
			}
			ctx, cancel := context.WithCancel(context.Background())
			//存入内存
			cache.GetIns().SubscribeManagers.Store(manager.ManagerID, &model.SubscribeManagerEx{
				SubscribeManager: manager,
				EquipmentIds:     eqs,
				TollgateIds:      tls,
				TaskIds:          tks,
				Ctx:              ctx,
				Cancel:           cancel,
			})
		}
		//查询订阅任务
		sbs, err := QuerySubscribes()
		if err != nil {
			logger.LOG_ERROR("查询订阅信息失败 ", err)
			return err
		}
		for _, sb := range sbs {
			cache.GetIns().SubscribeTasks.Store(sb.SubscribeID, sb)
		}
		return nil
	}, -1, time.Second*time.Duration(1))
}

func InitDB() (*sql.DB, error) {
	//db, err := sql.Open("mysql", "root:Netposa123@tcp(172.16.129.200:3307)/db_test001?multiStatements=true&charset=utf8")
	//db, err := sql.Open("mysql", "root:Netposa123@tcp(172.16.129.54:3307)/data_subscribe?multiStatements=true&charset=utf8")
	db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/data_subscribe?multiStatements=true&charset=utf8", userName, password, dsn))
	if err != nil {
		return nil, err
	}
	return db, err
}

func UpdateResource(addRes, delRes, taskRes []*model.ViewLib) error {
	tx, err := Conn.Beginx() // 开启事务
	if err != nil {
		logger.LOG_ERROR("begin trans failed, err:  ", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			logger.LOG_ERROR("panic  rollback , ", p)
		} else if err != nil {
			logger.LOG_ERROR("rollback")
			_ = tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			logger.LOG_WARN("commit")
		}
	}()
	//更新转发任务
	if len(taskRes) > 0 {
		task := taskRes[0]
		//先删转发再添加
		sql := "delete from view_info  WHERE MANAGERID=? AND  TYPE_ = ?"
		_, err := Conn.Exec(sql, task.MangerID, task.Type_)
		if err != nil {
			return err
		}
		for _, task := range taskRes {
			sql := "INSERT INTO view_info value (?,?,?)" 
			_, err := Conn.Exec(sql, task.MangerID, task.Type_,task.ResourceID)
			if err != nil {
				return err
			}
		}
	}
	//删除之前绑定的设备
	if len(delRes) > 0 {

		ids := make([]string, 0)
		for _, delRes := range delRes {
			ids = append(ids, delRes.ResourceID)
		}
		managerId := delRes[0].MangerID
		query, args, err := sqlx.In("DELETE FROM view_info WHERE RESOURCE_ID IN (?) AND MANAGERID = "+managerId+"", ids)
		if err != nil {
			logger.LOG_ERROR("构造sql失败", err)
			return err
		}
		query = tx.Rebind(query) // Rebind query
		_, err = tx.Exec(query, args...)
		if err != nil {
			logger.LOG_ERROR("执行sql失败,error : ", err, "query为  :", query, "args 为 ：", args)
			return err
		}
	}
	//插入新绑定的任务与设备
	if len(addRes) > 0 {
		// 存放 (?, ?) 的slice
		valueStrings := make([]string, 0, len(addRes))
		// 存放values的slice
		valueArgs := make([]interface{}, 0, len(addRes)*3)
		// 组装数据
		for _, u := range addRes {
			// 此处占位符要与插入值的个数对应
			valueStrings = append(valueStrings, "(?, ?,?)")
			valueArgs = append(valueArgs, u.MangerID)
			valueArgs = append(valueArgs, u.Type_)
			valueArgs = append(valueArgs, u.ResourceID)
		}
		stmt := fmt.Sprintf("INSERT INTO view_info(MANAGERID,TYPE_,RESOURCE_ID) VALUES %s",
			strings.Join(valueStrings, ","))
		_, err = tx.Exec(stmt, valueArgs...)
		if err != nil {
			logger.LOG_ERROR("执行sql失败,error : ", err, "  sql 为 : ", stmt)
			return err
		}
	}
	return nil
}

func InsertSubscribe(s *model.SubscribeLib) error {
	sql := "INSERT INTO subscribe VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
	_, err := Conn.Exec(sql, s.SubscribeID, s.Title, s.SubscribeDetail, s.ResourceURI, s.ApplicantName, s.ApplicantOrg, s.BeginTime, s.EndTime, s.ReceiveAddr, s.ReportInterval, s.Subscribe.Reason, s.Subscribe.OperateType, s.SubscribeStatus, s.SubscribeCancelOrg, s.SubscribeCancelPerson, s.CancelTime, s.CancelReason, s.TabID, s.ManagerID, s.ManagerName, s.UpdateTime, s.InsertTime,"")
	if err != nil {
		logger.LOG_ERROR("新增订阅信息失败  ", err)
		return err
	}
	return nil
}

//根据视图库id查询
func QueryByViewId(id string) (*model.ViewInfoVo, error) {
	sqlStr := "SELECT * FROM view_info WHERE MANAGERID=?"
	var viewLibs = make([]*model.ViewLib, 0)
	err := Conn.Select(&viewLibs, sqlStr, id)
	if err != nil {
		logger.LOG_ERROR("查询已绑定资源失败 ", err)
		return nil, err
	}
	var es = make([]string, 0)
	var tgs = make([]string, 0)
	var ts = make([]string, 0)
	for _, v := range viewLibs {
		switch v.Type_ {
		case constant.TYPE_EQUIPMENT:
			es = append(es, v.ResourceID)
		case constant.TYPE_TOLLGATE:
			tgs = append(tgs, v.ResourceID)
		case constant.TYPE_TASK:
			ts = append(ts, v.ResourceID)
		}
	}
	return &model.ViewInfoVo{
		ManagerID:    id,
		EquipmentIds: es,
		TollgateIds:  tgs,
		TaskIds:      ts,
	}, nil
}

//根據視圖庫id查询订阅信息表
func QuerySubscribeByManagerId(managerId string) ([]*model.SubscribeLib, error) {
	var subs = make([]*model.SubscribeLib, 0)
	sql := "SELECT * FROM subscribe WHERE ManagerID = ?"
	err := Conn.Select(&subs, sql, managerId)
	if err != nil {
		logger.LOG_ERROR("根据视图库id查询所有订阅信息失败", err)
		return nil, err
	}
	return subs, nil
}

func UpdateSubscribeStatusBySubscribeID(subscribeID string, subscribeStatus string, failDetail string) error {
	sql := "UPDATE subscribe SET SubscribeStatus=?,FailDetail=? WHERE SubscribeID=?"
	_, err := Conn.Exec(sql, subscribeStatus, failDetail, subscribeID)
	if err != nil {
		logger.LOG_WARN("订阅信息更新失败", err)
		return err
	}
	return nil
}
func UpdateOperateTypeBySubscribeID(subscribeID string, operateType string, subscribeStatus string) error {
	sql := "UPDATE subscribe SET OperateType=? ,SubscribeStatus=? WHERE SubscribeID=?"
	_, err := Conn.Exec(sql, operateType, subscribeStatus, subscribeID)
	if err != nil {
		logger.LOG_WARN("订阅信息更新失败", err)
		return err
	}
	return nil
}

//根据id查询订阅信息
func QuerySubscribeById(id string) (*model.SubscribeLib, error) {

	sql := "SELECT * FROM subscribe WHERE SubscribeID= ?"
	var sub = &model.SubscribeLib{}
	err := Conn.Get(sub, sql, id)
	if err != nil {
		logger.LOG_ERROR("查询订阅信息失败", err)
		return nil, err
	}
	return sub, nil
}

//查询所有订阅信息
func QuerySubscribes() ([]*model.SubscribeLib, error) {
	var subs = make([]*model.SubscribeLib, 0)
	sql := "SELECT * FROM subscribe"
	err := Conn.Select(&subs, sql)
	if err != nil {
		logger.LOG_ERROR("查询所有订阅信息失败", err)
		return nil, err
	}
	return subs, nil
}

func DeleteSubcribeById(id string) error {

	sqlStr := "DELETE FROM subscribe WHERE SubscribeID  = ?"
	_, e := Conn.Exec(sqlStr, id)
	if e != nil {
		logger.LOG_ERROR("删除订阅数据出错：", e)
		return e
	}
	return nil
}
func UpdateSubscribe(s *model.SubscribeLib) error {
	sql := "UPDATE subscribe  SET SubscribeID=?, Title=?, SubscribeDetail=?, ResourceURI=?, ApplicantName=?, ApplicantOrg=?, BeginTime=?, EndTime=?, ReceiveAddr=?, ReportInterval=?, Reason=?, OperateType=?, SubscribeStatus=?, SubscribeCancelOrg=?, SubscribeCancelPerson=?, CancelTime=?, CancelReason=?, TabID=?, ManagerID=?, ManagerName=?, UpdateTime=?, InsertTime=? WHERE ManagerID=?"
	_, err := Conn.Exec(sql, s.Subscribe.SubscribeID, s.Subscribe.Title, s.Subscribe.SubscribeDetail, s.Subscribe.ResourceURI, s.Subscribe.ApplicantName, s.Subscribe.ApplicantOrg, s.Subscribe.BeginTime, s.Subscribe.EndTime, s.Subscribe.ReceiveAddr, s.Subscribe.ReportInterval, s.Subscribe.Reason, s.Subscribe.OperateType, s.Subscribe.SubscribeStatus, s.Subscribe.SubscribeCancelOrg, s.Subscribe.SubscribeCancelPerson, s.Subscribe.CancelTime, s.Subscribe.CancelReason, s.TabID, s.ManagerName, s.UpdateTime, s.InsertTime, s.ManagerID)
	if err != nil {
		logger.LOG_ERROR("更新订阅失败", err)
		return err
	}
	return nil
}
func UpdateSubscribeBySubscribeID(subscribeID string, s *model.SubscribeLib) error {
	sql := "UPDATE subscribe  SET  Title=?, SubscribeDetail=?, ResourceURI=?, ApplicantName=?, ApplicantOrg=?, BeginTime=?, EndTime=?, ReceiveAddr=?, ReportInterval=?, Reason=?, OperateType=?, SubscribeStatus=?, SubscribeCancelOrg=?, SubscribeCancelPerson=?, CancelTime=?, CancelReason=?, TabID=?, ManagerID=?, ManagerName=?, UpdateTime=?, InsertTime=? WHERE SubscribeID=?"
	_, err := Conn.Exec(sql, s.Title, s.SubscribeDetail, s.ResourceURI, s.ApplicantName, s.ApplicantOrg, s.BeginTime, s.EndTime, s.ReceiveAddr, s.ReportInterval, s.Reason, s.OperateType, s.SubscribeStatus, s.SubscribeCancelOrg, s.SubscribeCancelPerson, s.CancelTime, s.CancelReason, s.TabID, s.ManagerID, s.ManagerName, s.UpdateTime, s.InsertTime, subscribeID)
	if err != nil {
		logger.LOG_ERROR("更新订阅失败", err)
		return err
	}
	return nil
}
func InsertManager(sm *model.SubscribeManager) error {
	sql := "INSERT INTO subscribe_manager VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
	_, err := Conn.Exec(sql, sm.ManagerID, sm.SelfManagerID, sm.AreaCode, sm.AreaName, sm.ManagerName, sm.ManagerIp, sm.ManagerPort, sm.ReceiveAddr, sm.UserName, sm.Password, sm.IsOnline, sm.DeviceStatus, sm.DataStatus, sm.ManagerRemark, sm.UpdateTime, sm.InsertTime, sm.ManagerType)
	if err != nil {
		logger.LOG_WARN("视图库入库失败", err)
		return err
	}
	return nil
}
func UpdateManager(sm *model.SubscribeManager) error {
	sql := "UPDATE subscribe_manager SET self_manager_id=?, area_code=?, area_name=?, manager_name=?, manager_ip=?, manager_port=?, receive_addr=?, user_name=?, password=?, is_online=?, device_status=?, data_status=?, manager_remark=?, update_time=?, manager_type=? where manager_id = ?;"
	_, e := Conn.Exec(sql, sm.SelfManagerID, sm.AreaCode, sm.AreaName, sm.ManagerName, sm.ManagerIp, sm.ManagerPort, sm.ReceiveAddr, sm.UserName, sm.Password, sm.IsOnline, sm.DeviceStatus, sm.DataStatus, sm.ManagerRemark, sm.UpdateTime, sm.ManagerType, sm.ManagerID)
	if e != nil {
		logger.LOG_WARN("图库更新失败", e)
		return e
	}
	return nil
}

func QueryManagerById(id string) (*model.SubscribeManager, error) {
	sqlstr := "SELECT * FROM subscribe_manager WHERE manager_id = ?"
	manager := &model.SubscribeManager{}
	err := Conn.Get(manager, sqlstr, id)
	if err != nil {
		logger.LOG_WARN("查询视图库失败 ,  ", err)
		return nil, err
	}
	return manager, nil
}
func QueryAllManager() ([]*model.SubscribeManager, error) {
	sqlstr := "SELECT * FROM subscribe_manager"
	var managers = make([]*model.SubscribeManager, 0)
	err := Conn.Select(&managers, sqlstr)
	if err != nil {
		logger.LOG_WARN("查询视图库失败 ,  ", err)
		return nil, err
	}
	return managers, nil
}

func DeleteSubcribeManagerById(id string) error {
	tx, err := Conn.Beginx() // 开启事务
	if err != nil {
		logger.LOG_ERROR("begin trans failed, err:  ", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			logger.LOG_ERROR("panic  rollback , ", p)
		} else if err != nil {
			logger.LOG_ERROR("rollback")
			_ = tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			logger.LOG_WARN("删除数据成功！ commit")
		}
	}()
	s1 := "DELETE FROM subscribe_manager WHERE manager_id  = ?"
	_, err = tx.Exec(s1, id)
	if err != nil {
		logger.LOG_ERROR("刪除视图库失败", err)
		return err
	}
	s2 := "DELETE FROM view_info WHERE MANAGERID  = ?"
	_, err = tx.Exec(s2, id)
	if err != nil {
		logger.LOG_ERROR("刪除资源绑定失败", err)
		return err
	}
	s3 := "DELETE FROM subscribe WHERE ManagerID = ?"
	_, err = tx.Exec(s3, id)
	if err != nil {
		logger.LOG_ERROR("刪除订阅任务失败", err)
		return err
	}
	return nil
}
